package org.jerrymouse.jsa4j.db.kv.derby;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class DBUtil {
	private String url;
	private Log log = LogFactory.getLog(DBUtil.class);

	public DBUtil(String url) {
		super();
		loadDriver();
		this.url = url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	private static String driver = "org.apache.derby.jdbc.EmbeddedDriver";

	public Connection getConnection() {
		try {
			Connection conn = DriverManager.getConnection(url);
			conn.setAutoCommit(false);
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void execute(String sql) throws SQLException {
		Connection connection = null;
		try {
			connection = getConnection();
			connection.createStatement().execute(sql);
			connection.commit();
		} finally {
			connection.close();
		}
	}

	private void loadDriver() {
		try {
			Class.forName(driver).newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// public ResultSet executeQuery(String sql) throws SQLException {
	// Connection connection = null;
	// try {
	// connection = getConnection();
	// ResultSet resultSet = connection.createStatement()
	// .executeQuery(sql);
	// connection.commit();
	// return resultSet;
	// } catch (SQLException e) {
	// e.printStackTrace();
	// } finally {
	// connection.close();
	// }
	// return null;
	// }
	public void createTable() {
		try {
			execute("create table keyvalue(id varchar(255), value CLOB ,PRIMARY KEY (id))");
		} catch (SQLException e) {
			log.warn("Table/View 'KEYVALUE' already exists in Schema 'APP'.");
		}
	}

}
